import datetime
import os

import openpyxl
import tools_pmc

wb = openpyxl.load_workbook(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\切换变更管理台账.xlsx")
ws = wb.worksheets[0]


def dict_colf(wbbb: openpyxl.workbook.workbook.Workbook):
    wsss = wbbb.worksheets[0]
    dict1 = {}
    for ii in range(1, wsss.max_column + 1):
        dict1[wsss.cell(2, ii).value] = ii
    return dict1


def a3():
    root_path = path = r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file"
    path_list = []
    for i in os.walk(root_path):
        for j in i[2]:
            # i[0]是当前文件夹的绝对路径，j是文件名
            path = os.path.join(i[0], j)
            if "物料信息表" in path and "source_file_Ⅱ" not in path and "专用" not in path:
                path_list.append(path)
    return path_list


path_list = a3()


def a1():
    return (ws.cell(i, 11).value + ws.cell(i, 12).value + ws.cell(i, 14).value) - \
        (ws.cell(i, 15).value + ws.cell(i, 16).value + ws.cell(i, 18).value)


def a2():
    str1 = ws.cell(i, 1).value
    aa = 0
    while str1 is None:
        aa += 1
        str1 = ws.cell(i - aa, 1).value
    data = str1[0:8]
    data = tools_pmc.detect_date(data, 3)
    data_str = data.strftime('%Y.%m.%d')
    bool1 = True
    path = ""
    while bool1:
        for ii in path_list:
            if data_str in ii:
                bool1 = False
                path = ii
        if bool1:
            data_str = (data + datetime.timedelta(days=1)).strftime('%Y.%m.%d')
    print(path)
    wbb = openpyxl.load_workbook(path)
    wss = wbb.worksheets[0]
    dict_col = dict_colf(wbb)
    bb = 0
    for ii in range(3, wss.max_row + 1):
        if int(wss.cell(ii, 1).value) == int(ws.cell(i, 8).value):
            bb = ii
    return wss.cell(bb, dict_col["可用数量"]).value, wss.cell(bb, dict_col["在途包括在检"]).value, \
        wss.cell(bb, dict_col["在检数量"]).value, wss.cell(bb, dict_col["已请未购量"]).value


def vlookup(lookup_value, table_array):
    for row in table_array:
        if str(row[0].value) == str(lookup_value[0:12]):
            return row[1].value, row[2].value, row[13].value, row[14].value, row[15].value, row[16].value


for i in range(3, ws.max_row + 1):
    if ws.cell(i, 8).value is not None:
        temp = vlookup(ws.cell(i, 8).value, tools_pmc.table_array_wlxxb)
        for j in range(9, 11):
            if ws.cell(i, j).value is None:
                ws.cell(i, j).value = temp[j - 9]
        if ws.cell(i, 11).value is None:
            temp = a2()
            for j in range(11, 15):
                if ws.cell(i, j).value is None:
                    ws.cell(i, j).value = temp[j - 11]
        temp = vlookup(ws.cell(i, 8).value, tools_pmc.table_array_wlxxb)
        if temp is not None:
            for j in range(15, 19):
                if ws.cell(i, j).value is None:
                    ws.cell(i, j).value = temp[j - 15]
        ws.cell(i, 19).value = a1()
    if ws.cell(i, 22).value is not None:
        temp = vlookup(ws.cell(i, 22).value, tools_pmc.table_array_wlxxb)
        if temp is not None:
            for j in range(23, 25):
                ws.cell(i, j).value = temp[j - 23]

ws.cell(1, 1).value = "报表更新日期" + tools_pmc.today_str1
wb.save(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\切换变更管理台账.xlsx")
print(tools_pmc.today_str1 + ":changed succeed!")
